{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a3fece35",
   "metadata": {},
   "source": [
    "## 调取高德API获取经纬度坐标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a000f727",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>城市</th>\n",
       "      <th>地址</th>\n",
       "      <th>项目名称</th>\n",
       "      <th>address</th>\n",
       "      <th>建筑面积/㎡</th>\n",
       "      <th>高德经度坐标</th>\n",
       "      <th>高德纬度坐标</th>\n",
       "      <th>gd_jingweidu</th>\n",
       "      <th>gd_lat</th>\n",
       "      <th>gd_lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5073.0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>常通路1号院1号楼</td>\n",
       "      <td>龙湖长楹天街</td>\n",
       "      <td>北京市常通路1号院1号楼龙湖长楹天街</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>116.599591</td>\n",
       "      <td>39.924542</td>\n",
       "      <td>(39.924972, 116.601492)</td>\n",
       "      <td>39.924972</td>\n",
       "      <td>116.601492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5070.0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>朝阳北路101号</td>\n",
       "      <td>朝阳大悦城</td>\n",
       "      <td>北京市朝阳北路101号朝阳大悦城</td>\n",
       "      <td>230000.0</td>\n",
       "      <td>116.519013</td>\n",
       "      <td>39.924522</td>\n",
       "      <td>(39.924195, 116.519951)</td>\n",
       "      <td>39.924195</td>\n",
       "      <td>116.519951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4873.0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>朝阳公园路6号</td>\n",
       "      <td>蓝色港湾</td>\n",
       "      <td>北京市朝阳公园路6号蓝色港湾</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>116.475697</td>\n",
       "      <td>39.949409</td>\n",
       "      <td>(39.949468, 116.475116)</td>\n",
       "      <td>39.949468</td>\n",
       "      <td>116.475116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5062.0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>东大桥路9号</td>\n",
       "      <td>侨福芳草地</td>\n",
       "      <td>北京市东大桥路9号侨福芳草地</td>\n",
       "      <td>47000.0</td>\n",
       "      <td>116.448831</td>\n",
       "      <td>39.919635</td>\n",
       "      <td>(39.920824, 116.448887)</td>\n",
       "      <td>39.920824</td>\n",
       "      <td>116.448887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4839.0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>黄村东大街38号院</td>\n",
       "      <td>大兴大悦春风里</td>\n",
       "      <td>北京市黄村东大街38号院大兴大悦春风里</td>\n",
       "      <td>106000.0</td>\n",
       "      <td>116.341819</td>\n",
       "      <td>39.730177</td>\n",
       "      <td>(39.730873, 116.341676)</td>\n",
       "      <td>39.730873</td>\n",
       "      <td>116.341676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>186.0</td>\n",
       "      <td>重庆市</td>\n",
       "      <td>中央公园同茂大道</td>\n",
       "      <td>龙湖中央公园天街</td>\n",
       "      <td>重庆市中央公园同茂大道龙湖中央公园天街</td>\n",
       "      <td>138034.0</td>\n",
       "      <td>106.573491</td>\n",
       "      <td>29.722005</td>\n",
       "      <td>(29.716535, 106.583819)</td>\n",
       "      <td>29.716535</td>\n",
       "      <td>106.583819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>103.0</td>\n",
       "      <td>重庆市</td>\n",
       "      <td>珠江路48号</td>\n",
       "      <td>龙湖西城天街</td>\n",
       "      <td>重庆市珠江路48号龙湖西城天街</td>\n",
       "      <td>77031.0</td>\n",
       "      <td>106.517462</td>\n",
       "      <td>29.510560</td>\n",
       "      <td>(29.509899, 106.51695)</td>\n",
       "      <td>29.509899</td>\n",
       "      <td>106.516950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>115.0</td>\n",
       "      <td>重庆市</td>\n",
       "      <td>邹容路100号</td>\n",
       "      <td>时代广场</td>\n",
       "      <td>重庆市邹容路100号时代广场</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>106.577094</td>\n",
       "      <td>29.558086</td>\n",
       "      <td>(29.558656, 106.577349)</td>\n",
       "      <td>29.558656</td>\n",
       "      <td>106.577349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>NaN</td>\n",
       "      <td>北京市</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西单更新场</td>\n",
       "      <td>北京市西单更新场</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(39.908269, 116.375344)</td>\n",
       "      <td>39.908269</td>\n",
       "      <td>116.375344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>NaN</td>\n",
       "      <td>深圳市</td>\n",
       "      <td>NaN</td>\n",
       "      <td>福田COCO Park</td>\n",
       "      <td>深圳市福田COCO Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(22.533627, 114.055071)</td>\n",
       "      <td>22.533627</td>\n",
       "      <td>114.055071</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>152 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id   城市         地址         项目名称              address    建筑面积/㎡  \\\n",
       "0    5073.0  北京市  常通路1号院1号楼       龙湖长楹天街   北京市常通路1号院1号楼龙湖长楹天街  270000.0   \n",
       "1    5070.0  北京市   朝阳北路101号        朝阳大悦城     北京市朝阳北路101号朝阳大悦城  230000.0   \n",
       "2    4873.0  北京市    朝阳公园路6号         蓝色港湾       北京市朝阳公园路6号蓝色港湾  150000.0   \n",
       "3    5062.0  北京市     东大桥路9号        侨福芳草地       北京市东大桥路9号侨福芳草地   47000.0   \n",
       "4    4839.0  北京市  黄村东大街38号院      大兴大悦春风里  北京市黄村东大街38号院大兴大悦春风里  106000.0   \n",
       "..      ...  ...        ...          ...                  ...       ...   \n",
       "147   186.0  重庆市   中央公园同茂大道     龙湖中央公园天街  重庆市中央公园同茂大道龙湖中央公园天街  138034.0   \n",
       "148   103.0  重庆市     珠江路48号       龙湖西城天街      重庆市珠江路48号龙湖西城天街   77031.0   \n",
       "149   115.0  重庆市    邹容路100号         时代广场       重庆市邹容路100号时代广场   54000.0   \n",
       "150     NaN  北京市        NaN        西单更新场             北京市西单更新场       NaN   \n",
       "151     NaN  深圳市        NaN  福田COCO Park       深圳市福田COCO Park       NaN   \n",
       "\n",
       "         高德经度坐标     高德纬度坐标             gd_jingweidu     gd_lat      gd_lng  \n",
       "0    116.599591  39.924542  (39.924972, 116.601492)  39.924972  116.601492  \n",
       "1    116.519013  39.924522  (39.924195, 116.519951)  39.924195  116.519951  \n",
       "2    116.475697  39.949409  (39.949468, 116.475116)  39.949468  116.475116  \n",
       "3    116.448831  39.919635  (39.920824, 116.448887)  39.920824  116.448887  \n",
       "4    116.341819  39.730177  (39.730873, 116.341676)  39.730873  116.341676  \n",
       "..          ...        ...                      ...        ...         ...  \n",
       "147  106.573491  29.722005  (29.716535, 106.583819)  29.716535  106.583819  \n",
       "148  106.517462  29.510560   (29.509899, 106.51695)  29.509899  106.516950  \n",
       "149  106.577094  29.558086  (29.558656, 106.577349)  29.558656  106.577349  \n",
       "150         NaN        NaN  (39.908269, 116.375344)  39.908269  116.375344  \n",
       "151         NaN        NaN  (22.533627, 114.055071)  22.533627  114.055071  \n",
       "\n",
       "[152 rows x 11 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from geopy.distance import geodesic\n",
    "\n",
    "def get_location(address):\n",
    "    url = 'https://restapi.amap.com/v3/geocode/geo'\n",
    "    params = {\n",
    "        'key': '91387bf84130bf737f313ac3a359e214',  # 请替换为你自己的高德地图 API Key\n",
    "        'address': address\n",
    "        \n",
    "    }\n",
    "\n",
    "    response = requests.get(url, params=params)\n",
    "    data = response.json()\n",
    "\n",
    "    if data['status'] == '1' and int(data['count']) > 0:\n",
    "        location = data['geocodes'][0]['location'].split(',')\n",
    "        latitude = float(location[1])\n",
    "        longitude = float(location[0])\n",
    "        return latitude, longitude\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "def get_lat(jingweudu):\n",
    "    if jingweudu is None:\n",
    "        return None\n",
    "    else:\n",
    "        return jingweudu[0] \n",
    "\n",
    "def get_lng(jingweudu):\n",
    "    if jingweudu is None:\n",
    "        return None\n",
    "    else:\n",
    "        return jingweudu[1]\n",
    "    \n",
    "def get_distance(x):\n",
    "    try:\n",
    "        distance = geodesic((x['Lat'], x['Lng']), (x['gd_lat'], x['gd_lng'])).meters\n",
    "        return distance\n",
    "    except Exception as e:\n",
    "        print(\"距离计算错误！\")\n",
    "        return \"Error\"\n",
    "\n",
    "file = r'C:\\Users\\NewUser\\Desktop'\n",
    "    \n",
    "# 导入地址文件\n",
    "df = pd.read_excel(r'C:\\Users\\NewUser\\Desktop\\副本高德经纬度(1).xlsx')\n",
    "\n",
    "# 爬取地址对应经纬度\n",
    "df['gd_jingweidu'] = df['address'].apply(get_location)\n",
    "df['gd_lat'] = df['gd_jingweidu'].apply(get_lat)\n",
    "df['gd_lng'] = df['gd_jingweidu'].apply(get_lng)\n",
    "\n",
    "# 计算距离并将结果存储在新列'distance'中\n",
    "# df['distance'] = df.apply(get_distance, axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3447a5f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(r'{}\\shop经纬度.xlsx'.format(file))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9d2b6be",
   "metadata": {},
   "source": [
    "## 腾讯地图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "52a9f77d",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>date</th>\n",
       "      <th>daytype</th>\n",
       "      <th>hour</th>\n",
       "      <th>laiyuandi_province</th>\n",
       "      <th>laiyuandi_city</th>\n",
       "      <th>laiyuandi_zone</th>\n",
       "      <th>address_test</th>\n",
       "      <th>address</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>renshu</th>\n",
       "      <th>renci</th>\n",
       "      <th>city</th>\n",
       "      <th>ds</th>\n",
       "      <th>mark</th>\n",
       "      <th>tx_lat</th>\n",
       "      <th>tx_lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>嘉定区</td>\n",
       "      <td>上海上海市嘉定区</td>\n",
       "      <td>中国,上海,上海市,嘉定区</td>\n",
       "      <td>121.265500</td>\n",
       "      <td>31.374730</td>\n",
       "      <td>7254</td>\n",
       "      <td>14343</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>31.37482</td>\n",
       "      <td>121.26621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>奉贤区</td>\n",
       "      <td>上海上海市奉贤区</td>\n",
       "      <td>中国,上海,上海市,奉贤区</td>\n",
       "      <td>121.474120</td>\n",
       "      <td>30.917900</td>\n",
       "      <td>6553</td>\n",
       "      <td>14854</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>30.91803</td>\n",
       "      <td>121.47410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>宝山区</td>\n",
       "      <td>上海上海市宝山区</td>\n",
       "      <td>中国,上海,上海市,宝山区</td>\n",
       "      <td>121.489100</td>\n",
       "      <td>31.404500</td>\n",
       "      <td>10119</td>\n",
       "      <td>19341</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>31.40527</td>\n",
       "      <td>121.48941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>图木舒克市</td>\n",
       "      <td>图木舒克市</td>\n",
       "      <td>新疆维吾尔自治区图木舒克市图木舒克市</td>\n",
       "      <td>中国,新疆维吾尔自治区,图木舒克市,图木舒克市</td>\n",
       "      <td>79.077978</td>\n",
       "      <td>39.867316</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>39.86495</td>\n",
       "      <td>79.06902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>徐汇区</td>\n",
       "      <td>上海上海市徐汇区</td>\n",
       "      <td>中国,上海,上海市,徐汇区</td>\n",
       "      <td>121.436760</td>\n",
       "      <td>31.188310</td>\n",
       "      <td>4559</td>\n",
       "      <td>10292</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>31.18826</td>\n",
       "      <td>121.43687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2386</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>齐齐哈尔市</td>\n",
       "      <td>碾子山区</td>\n",
       "      <td>黑龙江省齐齐哈尔市碾子山区</td>\n",
       "      <td>中国,黑龙江省,齐齐哈尔市,碾子山区</td>\n",
       "      <td>122.881830</td>\n",
       "      <td>47.516620</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>47.51698</td>\n",
       "      <td>122.88777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2387</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>齐齐哈尔市</td>\n",
       "      <td>讷河市</td>\n",
       "      <td>黑龙江省齐齐哈尔市讷河市</td>\n",
       "      <td>中国,黑龙江省,齐齐哈尔市,讷河市</td>\n",
       "      <td>124.877130</td>\n",
       "      <td>48.483880</td>\n",
       "      <td>59</td>\n",
       "      <td>118</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>48.48419</td>\n",
       "      <td>124.88397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2388</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>齐齐哈尔市</td>\n",
       "      <td>铁锋区</td>\n",
       "      <td>黑龙江省齐齐哈尔市铁锋区</td>\n",
       "      <td>中国,黑龙江省,齐齐哈尔市,铁锋区</td>\n",
       "      <td>123.978210</td>\n",
       "      <td>47.340750</td>\n",
       "      <td>39</td>\n",
       "      <td>44</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>47.34076</td>\n",
       "      <td>123.97807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2389</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>齐齐哈尔市</td>\n",
       "      <td>龙江县</td>\n",
       "      <td>黑龙江省齐齐哈尔市龙江县</td>\n",
       "      <td>中国,黑龙江省,齐齐哈尔市,龙江县</td>\n",
       "      <td>123.205320</td>\n",
       "      <td>47.338680</td>\n",
       "      <td>39</td>\n",
       "      <td>187</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>47.33737</td>\n",
       "      <td>123.20483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2390</th>\n",
       "      <td>10008</td>\n",
       "      <td>嘉善</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>齐齐哈尔市</td>\n",
       "      <td>龙沙区</td>\n",
       "      <td>黑龙江省齐齐哈尔市龙沙区</td>\n",
       "      <td>中国,黑龙江省,齐齐哈尔市,龙沙区</td>\n",
       "      <td>123.957520</td>\n",
       "      <td>47.317760</td>\n",
       "      <td>23</td>\n",
       "      <td>46</td>\n",
       "      <td>V0330400</td>\n",
       "      <td>202201</td>\n",
       "      <td>月客流</td>\n",
       "      <td>47.31742</td>\n",
       "      <td>123.95763</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2391 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id name date daytype hour laiyuandi_province laiyuandi_city  \\\n",
       "0     10008   嘉善    -       -    -                 上海            上海市   \n",
       "1     10008   嘉善    -       -    -                 上海            上海市   \n",
       "2     10008   嘉善    -       -    -                 上海            上海市   \n",
       "3     10008   嘉善    -       -    -           新疆维吾尔自治区          图木舒克市   \n",
       "4     10008   嘉善    -       -    -                 上海            上海市   \n",
       "...     ...  ...  ...     ...  ...                ...            ...   \n",
       "2386  10008   嘉善    -       -    -               黑龙江省          齐齐哈尔市   \n",
       "2387  10008   嘉善    -       -    -               黑龙江省          齐齐哈尔市   \n",
       "2388  10008   嘉善    -       -    -               黑龙江省          齐齐哈尔市   \n",
       "2389  10008   嘉善    -       -    -               黑龙江省          齐齐哈尔市   \n",
       "2390  10008   嘉善    -       -    -               黑龙江省          齐齐哈尔市   \n",
       "\n",
       "     laiyuandi_zone        address_test                  address         Lng  \\\n",
       "0               嘉定区            上海上海市嘉定区            中国,上海,上海市,嘉定区  121.265500   \n",
       "1               奉贤区            上海上海市奉贤区            中国,上海,上海市,奉贤区  121.474120   \n",
       "2               宝山区            上海上海市宝山区            中国,上海,上海市,宝山区  121.489100   \n",
       "3             图木舒克市  新疆维吾尔自治区图木舒克市图木舒克市  中国,新疆维吾尔自治区,图木舒克市,图木舒克市   79.077978   \n",
       "4               徐汇区            上海上海市徐汇区            中国,上海,上海市,徐汇区  121.436760   \n",
       "...             ...                 ...                      ...         ...   \n",
       "2386           碾子山区       黑龙江省齐齐哈尔市碾子山区       中国,黑龙江省,齐齐哈尔市,碾子山区  122.881830   \n",
       "2387            讷河市        黑龙江省齐齐哈尔市讷河市        中国,黑龙江省,齐齐哈尔市,讷河市  124.877130   \n",
       "2388            铁锋区        黑龙江省齐齐哈尔市铁锋区        中国,黑龙江省,齐齐哈尔市,铁锋区  123.978210   \n",
       "2389            龙江县        黑龙江省齐齐哈尔市龙江县        中国,黑龙江省,齐齐哈尔市,龙江县  123.205320   \n",
       "2390            龙沙区        黑龙江省齐齐哈尔市龙沙区        中国,黑龙江省,齐齐哈尔市,龙沙区  123.957520   \n",
       "\n",
       "            Lat  renshu  renci      city      ds mark    tx_lat     tx_lng  \n",
       "0     31.374730    7254  14343  V0330400  202201  月客流  31.37482  121.26621  \n",
       "1     30.917900    6553  14854  V0330400  202201  月客流  30.91803  121.47410  \n",
       "2     31.404500   10119  19341  V0330400  202201  月客流  31.40527  121.48941  \n",
       "3     39.867316       3      8  V0330400  202201  月客流  39.86495   79.06902  \n",
       "4     31.188310    4559  10292  V0330400  202201  月客流  31.18826  121.43687  \n",
       "...         ...     ...    ...       ...     ...  ...       ...        ...  \n",
       "2386  47.516620      10     10  V0330400  202201  月客流  47.51698  122.88777  \n",
       "2387  48.483880      59    118  V0330400  202201  月客流  48.48419  124.88397  \n",
       "2388  47.340750      39     44  V0330400  202201  月客流  47.34076  123.97807  \n",
       "2389  47.338680      39    187  V0330400  202201  月客流  47.33737  123.20483  \n",
       "2390  47.317760      23     46  V0330400  202201  月客流  47.31742  123.95763  \n",
       "\n",
       "[2391 rows x 19 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from geopy.distance import geodesic\n",
    "\n",
    "def get_coordinates(address):\n",
    "    url = 'https://apis.map.qq.com/ws/geocoder/v1/'\n",
    "    params = {\n",
    "        'address': address,\n",
    "        'key': 'MQLBZ-EEDLG-WDNQW-QD3ZD-DSD23-YHBD5'  # 在腾讯地图开放平台申请的API密钥\n",
    "    }\n",
    "\n",
    "    response = requests.get(url, params=params)\n",
    "    data = response.json()\n",
    "\n",
    "    if data['status'] == 0:\n",
    "        location = data['result']['location']\n",
    "        latitude = location['lat']\n",
    "        longitude = location['lng']\n",
    "        return latitude, longitude\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "# def get_distance(x):\n",
    "#     try:\n",
    "#         distance = geodesic((x['Lat'], x['Lng']), (x['tx_lat'], x['tx_lng'])).meters\n",
    "#         return distance\n",
    "#     except Exception as e:\n",
    "#         print(\"距离计算错误！\")\n",
    "#         return \"Error\"\n",
    "\n",
    "file = 'D:\\sunx\\取数需求\\wk25'\n",
    "    \n",
    "# 导入地址文件\n",
    "df = pd.read_excel(r'{}\\嘉善-来源地区县.xlsx'.format(file))\n",
    "\n",
    "def apply_get_coordinates(address):\n",
    "    time.sleep(0.5)  # 添加延迟，降低请求频率\n",
    "    return pd.Series(get_coordinates(address), dtype='float64')\n",
    "\n",
    "\n",
    "# 爬取地址对应经纬度\n",
    "df[['tx_lat', 'tx_lng']] = df['address_test'].apply(apply_get_coordinates)\n",
    "\n",
    "# 计算距离并将结果存储在新列'distance'中\n",
    "# df['distance'] = df.apply(get_distance, axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "631ff033",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(r'{}\\test2.xlsx'.format(file))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f615539e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22ddcbd8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "decabb68",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d44f1f0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2cfbf89",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b793ce30",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "414.391px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
